package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.Contact;
import br.com.ffm.praticas.connection.ConnectionDB;
import br.com.ffm.praticas.constants.Constant;

public class ContactDAO {
	
	/**
	 * Method that returns all Contacts
	 * @return List<Contact>
	 * @throws SQLException
	 */
	public List<Contact> getContacts() throws SQLException {
		String query = "SELECT * FROM contact";
		List<Contact> list = new LinkedList<Contact>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			Contact contact = new Contact();
			contact.setId(result.getInt("id"));
			if (result.getString("email") != null)
				contact.setEmail(result.getString("email"));
			if (result.getString("home_phone") != null)
				contact.setHomePhoneNumber(result.getString("home_phone"));
			if (result.getString("business_phone") != null)
				contact.setBusinessPhoneNumber(result.getString("business_phone"));
			if (result.getString("cell_phone") != null)
				contact.setCellPhoneNumber(result.getString("cell_phone"));
			list.add(contact);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that returns a specific Contact for your id
	 * @param id Contact Identifier
	 * @return Contact
	 * @throws SQLException
	 */
	public Contact getContact(int id) throws SQLException {
		String query = "SELECT * FROM contact WHERE (id = " + id + ")";
		Contact contact;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			contact = new Contact();
			contact.setId(result.getInt("id"));
			if (result.getString("email") != null)
				contact.setEmail(result.getString("email"));
			if (result.getString("home_phone") != null)
				contact.setHomePhoneNumber(result.getString("home_phone"));
			if (result.getString("business_phone") != null)
				contact.setBusinessPhoneNumber(result.getString("business_phone"));
			if (result.getString("cell_phone") != null)
				contact.setCellPhoneNumber(result.getString("cell_phone"));
			ConnectionDB.getInstance().close();
			return contact;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that inserts a Contact
	 * @param contact Contact that will be inserted
	 * @return Contact id
	 * @throws SQLException
	 */
	public int insert(Contact contact) throws SQLException {
		String query = "INSERT INTO contact (email, home_phone, business_phone, cell_phone) VALUES (";
		query += "'" + contact.getEmail() + "', ";
		query += "'" + contact.getHomePhoneNumber() + "', ";
		query += "'" + contact.getBusinessPhoneNumber() + "', ";
		query += "'" + contact.getCellPhoneNumber() + "')";
		ConnectionDB.getInstance().executeCommand(query);
		
		query = "SELECT MAX(id) FROM contact";
		int contactId = ConnectionDB.getInstance().executeScalar(query, Constant.SCALAR_COMMAND_MAX, "id");
		return contactId;
	}
	
	/**
	 * Method that deletes a Contact
	 * @param contact Contact that will be deleted
	 * @return true it will have been deleted or false if it will not be deleted
	 * @throws SQLException
	 */
	public boolean delete(Contact contact) {
		String query = "DELETE FROM contact WHERE (id = " + contact.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that updates a Contact
	 * @param contact Contact that will be brought up to date
	 * @return true it will have been updated or false if it will not be updated
	 * @throws SQLException
	 */
	public boolean update(Contact contact) {
		String query = "UPDATE contact SET ";
		query += "email = '" + contact.getEmail() + "', ";
		query += "home_phone = '" + contact.getHomePhoneNumber() + "', ";
		query += "business_phone = '" + contact.getBusinessPhoneNumber() + "', ";
		query += "cell_phone = '" + contact.getCellPhoneNumber() + "' ";
		query += "WHERE (id = " + contact.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	

}
